import time

import openpyxl
import pymysql
import requests
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from pymysql.err import IntegrityError
if __name__ == '__main__':
    # "mysql": {"db": "snkr", "ip": "106.55.41.183", "port": "3306", "user": "root", "passwd": "pj20211112"}
    conn = pymysql.connect(
        host="106.55.41.183",
        port=3306,
        user="root",
        password="pj20211112",
        db="snkr",

    )
    cur = conn.cursor()
    for i in range(1,260):
        time.sleep(1)
        url = "https://buff.163.com/api/market/goods?game=csgo&category_group=pistol&page_num=%s&use_suggestion=0&trigger=undefined_trigger&_=1648866278947&page_size=80"%(i)
        payload = {}
        headers = {
            'authority': 'buff.163.com',
            'accept': 'application/json, text/javascript, */*; q=0.01',
            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36',
            'x-requested-with': 'XMLHttpRequest',
            'sec-fetch-site': 'same-origin',
            'sec-fetch-mode': 'cors',
            'sec-fetch-dest': 'empty',
            'referer': 'https://buff.163.com/market/csgo',
            'accept-language': 'zh-CN,zh;q=0.9',
            'Cookie': 'Device-Id=CAd21nM8ZRbdoPzfL2oQ; game=csgo; Locale-Supported=zh-Hans; _ntes_nuid=80bf564af1287243de2a9a3e975fa751; NTES_CMT_USER_INFO=480610684%7C%E6%9C%89%E6%80%81%E5%BA%A6%E7%BD%91%E5%8F%8B0sFoBY%7Chttp%3A%2F%2Fcms-bucket.nosdn.127.net%2F2018%2F08%2F13%2F078ea9f65d954410b62a52ac773875a1.jpeg%7Cfalse%7CeWQuMGQ2OGMzOTRlYjlhNGQxNmJAMTYzLmNvbQ%3D%3D; __bid_n=1857aa85498ee403cf4207; FEID=v10-86490eee804e7a3ff832ba17063e4a09ee1fdb7e; __xaf_fpstarttimer__=1672800130299; __xaf_fptokentimer__=1672800130496; __xaf_thstime__=1672800130513; FPTOKEN=I0SbtjvTN+hFfD2AV1MivQLZYv09YO0UfBAojurY3co9T8XbmVpRPyl6abjpCujygqlifmMb89fCQBF+2tcF7ZgEfGPKJmhxjCCcSLqe5KRyhAlOT0I0r6K5msFLxjVnegVOz56yJz2ul7xCtU0Gg8k8Xfma8HoZ/5Oh7Pdgwwd8Nf2W8HsJ2alhWDzjCwNA8yR8612wOtXNcmCiNe65PWkVvpCy+mkzHBDeC5FPnetG0OGH5Yu5CWfH0+HdtufYmZzQLsfi2JE8+xP4nxOEukXdOe7Lrk1oaIVr6WSP1sklZec8fn9+GpXcOZxsUHz/u9fyE8Box0OhpfLftAGaWRXR0wDGVqLbSTji7NhSO/2hiyytIxkPwLlPkTOdFQBEdIfLMkmete4+QmRYv/boMA==|vcw3TpgMMDOhV2Hn12YRwLqv1qSkcnsS5qrtmEbEiK0=|10|18ee5dc23cbe5d920c44337b42f5c3e6; NTES_YD_SESS=fjS5nfecbVrrnvELxCriDPEix4mh1FL6VkxQHKErHawBeypJekSXAh0YrraI9CdZz9eyMFhSHm7oQWbKcx6FP.SGFP60bLdvY9HF_EWuz5BZziG3fNQcxxypz.dcBpPzioTvzwvy4lMxORehYGCSvtT8LQfnqtD9dWcQx1JmjqTxhwpgZP.ttEiW0nV6Owl_xlgkjmzNwDa4L.R079OoBQ5.S_wwGhTyBT6RmS3lzdJ.R; S_INFO=1678169840|0|0&60##|13713528136; P_INFO=13713528136|1678169840|1|netease_buff|00&99|null&null&null#gud&440300#10#0|&0||13713528136; remember_me=U1097174968|KsqJuKfQvPwDyYVBhMagW2s5hVQYzbHR; session=1-j9qfjVxbrd2gche9qXlK21bSADEC2QRR_t3ICW2WwFpQ2041254112; csrf_token=IjdmMjVjNjg2Y2ViMDc3NDIwZjFjNjkwMGNmZjdmNjMwMzQyOWM3MDIi.Fuhouw.qQ5Ez5qENyGV55Of5iMYPMjS_wU'
        }

        response = requests.request("GET", url, headers=headers, data=payload)
        rejson = response.json()
        l = []
        print(rejson)
        for item in rejson["data"]["items"]:
            cur.execute("select * from stock_info where id=%s"%(item["id"]))
            if not cur.fetchone():
                l.append((item["id"], item["name"], item["quick_price"], int(time.time()), int(time.time())))
                print(item["id"], item["name"], item["quick_price"])
        try:
            cur.executemany("insert into stock_info(id,name,quick_price,ctime,mtime,deleted) values(%s,%s,%s,%s,%s,0)", l)
            conn.commit()
        except(IntegrityError):
            pass
    print("done")
